package com.fjnu.client.dao;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.fjnu.client.entity.po.FarmlandManagePO;
import com.fjnu.client.entity.vo.FarmlandVO;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface FarmlandManageDao extends BaseMapper<FarmlandManagePO> {

    @Select("SELECT " +
            "distinct tmp.farmland_id, " +
            "tmp.farm_Id, " +
            "tmp.num farmland_num, " +
            "tmp.farmland_name, " +
            "tmp.area farmland_area, " +
            "tmp.farmer_id, " +
            "tmp.farmer_name " +
            "FROM " +
            "( " +
            "SELECT " +
            "farmland.id farmland_id, " +
            "farmland.type, " +
            "farmland.num, " +
            "farmland.`name` farmland_name, " +
            "farmland.area, " +
            "farmer.id farmer_id, " +
            "farmer.`name` farmer_name, " +
            "farm.id farm_id " +
            "FROM " +
            "iwea_farmer farmer, " +
            "iwea_farmland farmland, " +
            "iwea_farm farm " +
            "WHERE " +
            "farmer.id = farmland.farmer " +
            "AND farmer.login = #{Login} " +
            "AND farmland.farm_id = farm.id " +
            ") tmp," +
            "iwea_farmland " +
            "WHERE " +
            "tmp.farm_id = iwea_farmland.farm_id " +
            "AND tmp.type = #{Type}")
    public List<FarmlandVO> getFarmlandMainInfo(@Param("Login")int login, @Param("Type")int type);

    @Select("SELECT " +
            " farmland.NAME farmland_name, " +
            " farmland.num farmland_num, " +
            " farmer.NAME farmer_name, " +
            " farmland.type farmland_type, " +
            " farmland.area farmland_area, " +
            " farmland.deepth farmland_deepth, " +
            " variety.NAME plant_name, " +
            " batch.num batch_num, " +
            " batch.start_time , " +
            " batch.plant_count, " +
            " batch.growth_stage, " +
            " batch.`status` batch_status, " +
            " variety.describetion, " +
            " hardware_data.`data`, " +
            " cost.`name` cost_name, " +
            " cost.consumption cost_consumption, " +
            " cost.unit cost_unit  " +
            "FROM " +
            " ( " +
            "SELECT " +
            " iwea_plant_batch.*  " +
            "FROM " +
            " ( SELECT max( batch_id ) AS batch_id FROM iwea_plant_history his WHERE his.farm_id = #{FarmID} AND his.farmland_id = #{FarmlandID}) tmp, " +
            " iwea_plant_batch  " +
            "WHERE " +
            " tmp.batch_id = iwea_plant_batch.id  " +
            " ) batch, " +
            " iwea_farmland farmland, " +
            " iwea_farmer farmer, " +
            " iwea_variety variety, " +
            " fog_device device, " +
            " fog_hardware_data hardware_data, " +
            " iwea_cost cost, " +
            " iwea_plant_history history  " +
            "WHERE " +
            " batch.plan_id = variety.id  " +
            " AND farmland.farmer = farmer.id  " +
            " AND farmland.farm_id = history.farm_id  " +
            " AND farmland.id = history.farmland_id  " +
            " AND farmland.id = #{FarmlandID}  " +
            " AND farmland.farm_id = #{FarmID}  " +
            " AND batch.id = history.batch_id  " +
            " AND farmland.farm_id = device.farm_id  " +
            " AND farmland.id = device.farmland_id  " +
            " AND device.id = hardware_data.device_id  " +
            " AND cost.farm_id = farmland.farm_id  " +
            " AND cost.farmland_id = farmland.id  " +
            " AND cost.batch_id = batch.id " +
            " AND farmland.type = #{Type}")
    public List<FarmlandVO> getIntelligentFarmlandDetailInfo( @Param("FarmlandID")int farmlandId,@Param("FarmID")int farmId, @Param("Type")int type);

    @Select("SELECT " +
            " farmland.name farmland_name, "+
            " farmland.num farmland_num, " +
            " farmer.NAME farmer_name, " +
            " farmland.type farmland_type, " +
            " farmland.area farmland_area, " +
            " farmland.deepth farmland_deepth, " +
            " variety.NAME plant_name, " +
            " batch.num batch_num, " +
            " batch.start_time, " +
            " batch.plant_count, " +
            " batch.growth_stage, " +
            " batch.`status` batch_status, " +
            " variety.describetion, " +
            " production_record.*  " +
            "FROM " +
            " ( " +
            "SELECT " +
            " iwea_plant_batch.*  " +
            "FROM " +
            " ( SELECT max( batch_id ) AS batch_id FROM iwea_plant_history his WHERE his.farm_id = #{FarmID}  AND his.farmland_id = #{FarmlandID} ) tmp, " +
            " iwea_plant_batch  " +
            "WHERE " +
            " tmp.batch_id = iwea_plant_batch.id  " +
            " ) batch, " +
            " iwea_farmland farmland, " +
            " iwea_farmer farmer, " +
            " iwea_variety variety, " +
            " iwea_cost cost, " +
            " iwea_plant_history history, " +
            " ( " +
            "SELECT " +
            " count( harvest.id ) batch_count, " +
            " SUM( harvest.count ) harvest_sum  " +
            "FROM " +
            " iwea_harvest harvest  " +
            "WHERE " +
            " harvest.farm_id =  #{FarmID}  " +
            " AND harvest.farmland_id = #{FarmlandID} " +
            " ) production_record  " +
            "WHERE " +
            " batch.plan_id = variety.id  " +
            " AND farmland.farmer = farmer.id  " +
            " AND farmland.farm_id = history.farm_id  " +
            " AND farmland.id = history.farmland_id  " +
            " AND farmland.id = #{FarmlandID}  " +
            " AND farmland.farm_id = #{FarmID}  " +
            " AND batch.id = history.batch_id  " +
            " AND cost.farm_id = farmland.farm_id  " +
            " AND cost.farmland_id = farmland.id  " +
            " AND cost.batch_id = batch.id  " +
            " AND farmland.type = #{Type}")
    public List<FarmlandVO> getTraditionalFarmlandDetailInfo( @Param("FarmlandID")int farmlandId,@Param("FarmID")int farmId,@Param("Type")int type);
}
